package io.itit.poi.excel;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 
 * @author skydu
 *
 */
public class ExcelUtils {
	//
	private static final Logger logger=LoggerFactory.getLogger(ExcelUtils.class);
	//
	private static final Class<?>[] supportFieldClasses={
			int.class,Integer.class,
			long.class,Long.class,
			short.class,Short.class,
			byte.class,Byte.class,
			boolean.class,Boolean.class,
			float.class,Float.class,
			double.class,Double.class,
			Date.class,
			String.class
	};
	//
	public static class Coordinate{
		public int x;
		public int y;
		//
		public Coordinate(){
			
		}
		//
		public Coordinate(int x,int y){
			this.x=x;
			this.y=y;
		}
	}
	//
	/**
	 * 
	 * @param list
	 * @return
	 * @throws Exception
	 */
	public static <T> ByteArrayOutputStream writeExcel(List<T> list) throws Exception {
		return writeExcel(null, list);
	}
	
	/**
	 * 
	 * @param list
	 * @param file
	 * @throws Exception
	 */
	public static <T> void writeExcelToFile(List<T> list,File file) throws Exception {
		writeExcelToFile(null, list, file);
	}
	
	/**
	 * 
	 * @param list
	 * @param file
	 * @throws Exception
	 */
	public static <T> void writeExcelToFile(String sheetName,List<T> list,File file) throws Exception {
		ByteArrayOutputStream bos=writeExcel(sheetName, list);
		if(bos==null){
			throw new RuntimeException("no data to write");
		}
		try(OutputStream outputStream = new FileOutputStream(file)) {
			bos.writeTo(outputStream);
		}
	}
	
	private static void checkFieldClass(Class<?> clazz){
		for (Class<?> supprtClass : supportFieldClasses) {
			if(supprtClass==clazz){
				return;
			}
		}
		throw new RuntimeException("Unsupport Field Class "+clazz.getSimpleName());
	}
	/**
	 * 
	 * @param sheetName
	 * @param list
	 * @return
	 * @throws Exception
	 */
	public static <T> ByteArrayOutputStream writeExcel(String sheetName,List<T> list) throws Exception {
		if (list == null || list.size() == 0) {
			return null;
		}
		try (XSSFWorkbook wb = new XSSFWorkbook()) {
			T test = list.get(0);
			Map<String, Field> fieldMap = new HashMap<String, Field>();
			List<String> titles = new ArrayList<>();
			Field[] fields = test.getClass().getDeclaredFields();
			for (Field field : fields) {
				if (field.isAnnotationPresent(ExcelCell.class)) {
					checkFieldClass(field.getType());
					ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
					fieldMap.put(excelCell.name(), field);
					titles.add(excelCell.name());
				}
			}
			if(fieldMap.size()!=titles.size()){
				throw new IllegalArgumentException("@ExcelCell name cannot same");
			}
			//
			Sheet sheet=null;
			if(sheetName==null){
				sheet = wb.createSheet();
			}else{
				sheet = wb.createSheet(sheetName);
			}
			//set header
			Row titleRow = sheet.createRow(0);
			for (int i = 0; i < titles.size(); i++) {
				Cell cell = titleRow.createCell(i);
				cell.setCellValue(titles.get(i));
			}
			//
			int totalRowCount = list.size();
			for (int r = 0; r < totalRowCount; r++) {
				T bean=list.get(r);
				Row row = sheet.createRow(r + 1);
				for (int c = 0; c < titles.size(); c++) {
					Cell cell = row.createCell(c);
					String title=titles.get(c);
					Field field=fieldMap.get(title);
					field.setAccessible(true);
					Object val=field.get(bean);
					if(val==null){
						continue;
					}
					ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
					if(field.getType()==Date.class){
						cell.setCellValue(new SimpleDateFormat(excelCell.dateFormat()).format((Date)val));
					}else{
						cell.setCellValue(val.toString());
					}
				}
			}
			//
			ByteArrayOutputStream bos = new ByteArrayOutputStream();
			wb.write(bos);
			bos.close();
			return bos;
		}
	}

	public static ByteArrayOutputStream writeExcel(TableData data) throws IOException {
		try (XSSFWorkbook wb = new XSSFWorkbook()) {
			XSSFSheet sheet = wb.createSheet(data.sheetName);
			XSSFRow row = sheet.createRow(0);
			for (int i = 0; i < data.headers.size(); i++) {
				XSSFCell cell = row.createCell(i);
				cell.setCellValue(data.headers.get(i));
			}
			//
			int idx = 1;
			for (List<String> ss : data.contents) {
				XSSFRow r = sheet.createRow(idx++);
				for (int i = 0; i < ss.size(); i++) {
					XSSFCell cell = r.createCell(i);
					cell.setCellValue(ss.get(i));
				}
			}
			ByteArrayOutputStream bos = new ByteArrayOutputStream();
			wb.write(bos);
			bos.close();
			return bos;
		}
	}
	
	/**
	 * 
	 * @param xlsxFile
	 * @param imageData
	 * @param col1
	 * @param row1
	 * @param col2
	 * @param row2
	 * @throws IOException
	 */
	public static void addPicture(File xlsxFile,byte[] imageData,
			int col1, int row1, int col2, int row2) throws IOException{
		try (XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(xlsxFile))) {
			XSSFSheet sheet = wb.getSheetAt(0);
			XSSFDrawing drawing=sheet.createDrawingPatriarch();
			XSSFClientAnchor anchor=new XSSFClientAnchor(0, 0,0, 0, col1, row1, col2, row2);
			drawing.createPicture(anchor, wb.addPicture(imageData,XSSFWorkbook.PICTURE_TYPE_JPEG));
			FileOutputStream fileOut = new FileOutputStream(xlsxFile);
			wb.write(fileOut);
			fileOut.close();
		}
	}
	
	/**
	 * 
	 * @param clazz
	 * @param in
	 * @return
	 * @throws Exception
	 */
	public static <T> List<T> readExcel(Class<T> clazz,InputStream in) throws Exception{
		try (XSSFWorkbook wb = new XSSFWorkbook(in)) {
			if (wb.getNumberOfSheets() <= 0) {
				return new ArrayList<>();
			}
			XSSFSheet sheet = wb.getSheetAt(0);
			//
			List<T> result = new ArrayList<T>(sheet.getLastRowNum() - 1);
            Row row = sheet.getRow(sheet.getFirstRowNum());
            //
            Map<String, Field> fieldMap = new HashMap<String, Field>();
            Map<String, String> titleMap = new HashMap<String, String>();
            //
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                if (field.isAnnotationPresent(ExcelCell.class)) {
                	ExcelCell mapperCell = field.getAnnotation(ExcelCell.class);
                    fieldMap.put(mapperCell.name(), field);
                }
            }

            for (Cell title : row) {
                CellReference cellRef = new CellReference(title);
                titleMap.put(cellRef.getCellRefParts()[2], title.getRichStringCellValue().getString());
            }

            for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
                T t = clazz.newInstance();
                Row dataRow = sheet.getRow(i);
                for (Cell data : dataRow) {
                    CellReference cellRef = new CellReference(data);
                    String cellTag = cellRef.getCellRefParts()[2];
                    String name = titleMap.get(cellTag);
                    Field field = fieldMap.get(name);
                    if (null != field) {
                        field.setAccessible(true);
                        setFiedlValue(data, t, field);
                    }
                }
                result.add(t);
            }
            //
            return result;
		}
	 }
	//
	public static TableData readExcel(InputStream in) throws Exception {
		TableData data = new TableData();
		try (XSSFWorkbook wb = new XSSFWorkbook(in)) {
			if (wb.getNumberOfSheets() <= 0) {
				return data;
			}
			XSSFSheet sheet = wb.getSheetAt(0);
			data.sheetName = sheet.getSheetName();
			if (sheet.getLastRowNum() <= 0) {
				return data;
			}
			XSSFRow row = sheet.getRow(sheet.getFirstRowNum());
			for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); i++) {
				XSSFCell cell = row.getCell(i);
				data.headers.add(getCellContent(cell));
			}
			//
			for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
				List<String> cc = new ArrayList<String>();
				data.contents.add(cc);
				XSSFRow rr = sheet.getRow(i);
				for (int j = rr.getFirstCellNum(); j <= rr.getLastCellNum(); j++) {
					XSSFCell cell = rr.getCell(j);
					cc.add(getCellContent(cell));
				}
			}
		}
		return data;
	}
	//
	private static String getCellContent(Cell cell){
		if(cell!=null){
			if(cell.getCellType()==XSSFCell.CELL_TYPE_BOOLEAN){
				return (cell.getBooleanCellValue()+"");
			}
			if(cell.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
				DecimalFormat df = new DecimalFormat("0");
				return (df.format(cell.getNumericCellValue()));
			}
			if(cell.getCellType()==XSSFCell.CELL_TYPE_STRING){
				return (cell.getStringCellValue()+"");
			}
		}
		return "";
	}
	//
	private static void setFiedlValue(Cell cell, Object o, Field field) throws IllegalAccessException, ParseException {
        String content=getCellContent(cell);
        if(content==null||content.length()==0){
        	return;
        }
        if(logger.isInfoEnabled()){
        	logger.info("cell:{}, field:{}, type:{} content:{}", 
        			cell.getCellType(), field.getName(), field.getType().getName(),content);
        }
        //
        Class<?> filedClass=field.getType();
        if(filedClass==int.class||filedClass==Integer.class){
        	field.set(o, Integer.valueOf(content));
        }else if(filedClass==short.class||filedClass==Short.class){
        	field.set(o, Short.valueOf(content));
        }else if(filedClass==float.class||filedClass==Float.class){
        	field.set(o, Float.valueOf(content));
        }else if(filedClass==double.class||filedClass==Double.class){
        	field.set(o, Double.valueOf(content));
        }else if(filedClass==byte.class||filedClass==Byte.class){
        	field.set(o, Byte.valueOf(content));
        }else if(filedClass==boolean.class||filedClass==Boolean.class){
        	field.set(o, Boolean.valueOf(content));
        }else if(filedClass==Date.class){
        	ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
        	field.set(o, new SimpleDateFormat(excelCell.dateFormat()).parse(content));
        }else if(filedClass==String.class){
        	field.set(o, content);
        }
    }
}
